package com.zr.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.TealsjlDaoljc;
import com.zr.model.JL;
import com.zr.model.TeaJL;
import com.zr.util.JDBCUtil;

public class TealsjlDaoImplljc implements TealsjlDaoljc{

	@Override
	public List<TeaJL> findTeaJLByTname(int page, int rows, String tname) {
		List<TeaJL> j = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT teablog.cname,teablog.id,teablog.nr,teablog.score,teablog.sid,teablog.sj,");
		sql.append(" teablog.sname,teablog.sqsj,teablog.tname,teablog.zt");
		sql.append(" FROM teablog WHERE tname=? LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				TeaJL t =new TeaJL();
				t.setCname(rs.getString("cname"));
				t.setNr(rs.getString("nr"));
				t.setId(rs.getInt("id"));
				t.setScore(rs.getString("score"));
				t.setSid(rs.getInt("sid"));
				t.setSj(rs.getString("sj"));
				t.setSname(rs.getString("sname"));
				t.setSqsj(rs.getString("sqsj"));
				t.setTname(rs.getString("tname"));
				t.setZt(rs.getString("zt"));
				j.add(t);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return j;
	}

	@Override
	public int countAllJLByTname(String tname) {
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(teablog.id) as count ");
		sql.append(" FROM teablog where tname=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count =Integer.parseInt(rs.getString("count"));
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public String findTnameBytid(int tid) {
		String tname = "";
		StringBuffer sql = new StringBuffer("");
		sql.append(" select tname from teacher");
		sql.append(" WHERE tid=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				tname = rs.getString("tname");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tname;
	}

}
